package com.distributeredis.redis_springboot.springpoi.service.impl;

import com.distributeredis.redis_springboot.springpoi.pojo.UserInfo;
import com.distributeredis.redis_springboot.springpoi.service.PoiService;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
@Service
public class PoiTestImpl implements PoiService {
    List<UserInfo> infos = Arrays.asList(
            new UserInfo("jfaljflaffl--001", "张三111", 16, "河南"),
            new UserInfo("jfaljflaffl--002", "张三222", 12, "郑州"),
            new UserInfo("jfaljflaffl--003", "张三333", 26, "洛阳"),
            new UserInfo("jfaljflaffl--004", "张三444", 6, "平顶山"),
            new UserInfo("jfaljflaffl--005", "张三555", 23, "广东"),
            new UserInfo("jfaljflaffl--006", "张三666", 19, "周口")
    );
    @Override
    public void poiTest1(HttpServletResponse response) throws IOException {
        // 创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        XSSFSheet sheet = workbook.createSheet("用户数据");
        // 设置列宽
        sheet.setColumnWidth(0,20*256);
        sheet.setColumnWidth(1,20*256);
        sheet.setColumnWidth(2,20*256);
        sheet.setColumnWidth(3,20*256);
        // 实现数据有效性的校验  对用户地址实现下拉框
        String[] explicitListValues = new String[]{"河南","郑州","洛阳","平顶山","广东","周口"};
        setValidationData(sheet,1,1048571,3,3,explicitListValues);
        // 设置数字格式的验证
        checkNumberValue(sheet);
        // 处理标题
        String[] titles = new String[]{"用户id","用户名","用户年龄","用户地址"};
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
        }
//        1.创建一个超链接样式对象
        XSSFCellStyle hyperlinkStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setUnderline(Font.U_SINGLE);
        font.setColor(IndexedColors.BLUE.getIndex());
        hyperlinkStyle.setFont(font);
        // 处理内容
        XSSFRow sheetRow = null;
        XSSFCell cell = null;
        int rowIndex = 1;
        for (UserInfo info : infos) {
            sheetRow = sheet.createRow(rowIndex);

            cell = sheetRow.createCell(0);  // 第一列
//            2.创建一个超链接对象，并设置链接地址
            XSSFCreationHelper creationHelper = workbook.getCreationHelper();
            XSSFHyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
            hyperlink.setAddress("https://www.baidu.com");
//            3.将超链接对象应用于单元格
            cell.setHyperlink(hyperlink);
            cell.setCellStyle(hyperlinkStyle);
            cell.setCellValue(info.getUserId());

            cell = sheetRow.createCell(1);  // 第二列
            cell.setCellValue(info.getUserName());

            cell = sheetRow.createCell(2);  // 第三列
            cell.setCellValue(info.getUserAge());

            cell = sheetRow.createCell(3);  // 第四列
            cell.setCellValue(info.getUserAddress());

            rowIndex ++;
        }
        //  导出的文件名称  高版本一个流两个头
        String filename="员工数据.xlsx";
        // 设置文件的打开方式和mime类型
        ServletOutputStream outputStream = response.getOutputStream();
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(outputStream);
    }

    private void checkNumberValue(XSSFSheet sheet) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置验证生效的范围
        CellRangeAddressList addressList = new CellRangeAddressList(1,1048571,2,2);
        //设置验证方式
        DataValidationConstraint constraint =
                helper.createDecimalConstraint(DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(0), String.valueOf(200));
        //创建验证对象
        DataValidation validation = helper.createValidation(constraint, addressList);
        //错误提示信息
        validation.createErrorBox("提示", "请输入【0-200】之间的两位小数");
        //设置是否显示错误窗口
        validation.setShowErrorBox(true);
        //验证和工作簿绑定
        sheet.addValidationData(validation);
    }

    /**
     * 添加数据有效性检查.
     * @param sheet 要添加此检查的Sheet
     * @param firstRow 开始行
     * @param lastRow 结束行
     * @param firstCol 开始列
     * @param lastCol 结束列
     * @param explicitListValues 有效性检查的下拉列表
     * @throws IllegalArgumentException 如果传入的行或者列小于0(< 0)或者结束行/列比开始行/列小
     */
    public void setValidationData(Sheet sheet, int firstRow, int lastRow,
                                         int firstCol, int lastCol, String[] explicitListValues) throws IllegalArgumentException{
        if (firstRow < 0 || lastRow < 0 || firstCol < 0 || lastCol < 0 || lastRow < firstRow || lastCol < firstCol) {
            throw new IllegalArgumentException("Wrong Row or Column index : " + firstRow+":"+lastRow+":"+firstCol+":" +lastCol);
        }
        if (sheet instanceof XSSFSheet) {
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(explicitListValues);
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        } else if(sheet instanceof HSSFSheet){
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(explicitListValues);
            DataValidation validation = new HSSFDataValidation(addressList, dvConstraint);
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }
    }
}
